SET SERVEROUTPUT ON SIZE 1000000;
SET VERIFY OFF

DECLARE
	l_total_extracted	NUMBER;
	l_total_imported	NUMBER;				
	l_func_id			VARCHAR2(100):='CAPFNDREC_ASSIGNMENT';

BEGIN
	SELECT COUNT(1)
	INTO l_total_extracted        
	FROM
		PER_BUSINESS_GROUPS			B,
		PER_ALL_ASSIGNMENTS_F		A,
		PER_JOBS					J,
		PER_ASSIGNMENT_STATUS_TYPES	S,
		HR_LOCATIONS				L,
		PER_ALL_PEOPLE_F			P1,
		PER_ALL_PEOPLE_F			P2,
		HR_ORGANIZATION_UNITS		O
	WHERE	SUBSTR(B.Name,4,4)			=	UPPER('&1')
	AND		A.Business_Group_ID			=	B.Business_Group_ID
	AND		A.Job_ID					=	J.Job_ID(+)
	AND		A.Assignment_Status_Type_ID	=	S.Assignment_Status_Type_ID(+)
	AND		A.Location_ID				=	L.Location_ID(+)
	AND		A.Person_ID					=	P1.Person_ID
	AND		A.Supervisor_ID				=	P2.Person_ID(+)
	AND		A.Organization_ID			=	O.Organization_ID(+)
	AND		SYSDATE		BETWEEN		A.Effective_Start_Date
						AND			A.Effective_End_Date
	AND		SYSDATE		BETWEEN		P1.Effective_Start_Date
						AND			P1.Effective_End_Date
	AND		SYSDATE		BETWEEN		P2.Effective_Start_Date(+)
						AND			P2.Effective_End_Date(+)
	ORDER BY	A.Person_ID;
	
	SELECT COUNT(1) 
	INTO l_total_imported
	FROM CRST_FND_ASSIGNMENT;

	dbms_output.put_line('Function: '||l_func_id);		
	dbms_output.put_line('Total Extracted: '||TO_CHAR(l_total_extracted)) ;
	dbms_output.put_line('Total Imported:  '||TO_CHAR(l_total_imported));
	IF l_total_extracted <> l_total_imported THEN
		dbms_output.put_line('Error: No. of Records do not match!');
	END IF;		
						
EXCEPTION
	WHEN OTHERS THEN	
		dbms_output.put_line('Function Error: '||l_func_id);			
END;
/
